﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_SearchDirtyHeader

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal Parent As String, ByVal VillageName As String, ByVal annual As String) As DataTable

        Dim sql As New MyOrganizationDataFilter(True)
        Dim dat As New clsData

        sql.AppendFormat("SELECT D.Active, D.LeaderID, A.Location as FullLocation,A.* ")
        sql.AppendFormat(",RegionName = B.TownName + B.RegionName,D.WorkStartDate,D.WorkEndDate,E.VOrganizationName,V.VolunteerName, D.TakeDate ")
        sql.AppendFormat(",LeaderName = leader.VolunteerName ")
        'sql.AppendFormat(",UOrganizationID = D.VUOrganizationID
        sql.AppendFormat(",UOrganizationID = dbo.GetUOrganizationID(orm.OrganizationID,-1) ")
        sql.AppendFormat(", B.FullName ")
        sql.AppendFormat("FROM FindDirtyHeader A ")
        sql.AppendFormat("LEFT JOIN Village B on A.VillageId=B.RegionID ")
        sql.AppendFormat("left join OrganizationRegionMapping orm on left(A.VillageID,3) = orm.RegionID ")
        'sql.AppendFormat("LEFT JOIN Region C on B.Parent=C.RegionID ")
        sql.AppendFormat("left JOIN WorkHeader D on A.WorkHeaderID=D.WorkHeaderID ")
        sql.AppendFormat("Left Join VOrganizationViewV2 E on D.VUOrganizationID='-1,'+cast(E.VOrganizationID as nvarchar(50)) ")
        sql.AppendFormat("left join Volunteer leader on D.LeaderID = leader.VolunteerID ")
        sql.AppendFormat("Left Join Volunteer V on A.CreateBy = V.VolunteerID ")
        sql.AppendFormat("WHERE ")
        sql.AppendFormat("(A.VillageID in (SELECT RegionID FROM dbo.Region WHERE Parent='{0}' OR '{0}' ='')) ", Parent)
        If VillageName <> "" Then
            sql.AppendFormat("AND (B.RegionID = '{0}' ) ", VillageName)
        End If
        'If townName <> "" Then
        '    sql.AppendFormat("AND B.TownName like '%{0}%' or '{0}'='') ", VillageName)

        '    If VillageName <> "" Then

        '    End If

        'End If

        If annual <> "" Then
            sql.AppendFormat("AND year(A.ReportDate) = '{0}' ", annual)
        End If

        sql.AppendFormat("order by FullName, ReportDate desc ")
        'MyObj.WriteErrorLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Delete(ByVal original_FindDirtyHeaderID As Integer, ByVal original_WorkHeaderID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.Length = 0

            sql.AppendFormat("DELETE whr from WorkHourRecord whr inner join WorkRecord wr on whr.RecordID = wr.RecordID and whr.WorkType='FindDirtyHeader' WHERE wr.WorkHeaderID={0} ", original_WorkHeaderID)
            sql.AppendFormat("DELETE WorkRecord WHERE WorkHeaderID = {0}", original_WorkHeaderID)
            sql.AppendFormat("DELETE WorkHeader WHERE WorkHeaderID = {0}", original_WorkHeaderID) '可省略PlanID因為WorkHeaderID在WorkHeader不重複

            dat.AccData(sql)

            scope.Complete()

        End Using

    End Sub
End Class
